package com.kaizeli.website.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.kaizeli.website.pojo.dto.ContractPersonnelExcelDTO;
import com.kaizeli.website.service.ContractPersonnelImportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * 合同人员导入服务实现类
 *
 * @author Kaizeli
 * @date 2024-10-21
 */
@Slf4j
@Service
public class ContractPersonnelImportServiceImpl implements ContractPersonnelImportService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Map<String, Object> importContractPersonnelExcel(MultipartFile file, boolean updateIfExists) {
        Map<String, Object> result = new HashMap<>();

        if (file == null || file.isEmpty()) {
            result.put("success", false);
            result.put("message", "文件为空，请上传Excel文件");
            return result;
        }

        String filename = file.getOriginalFilename();
        if (filename == null || (!filename.endsWith(".xlsx") && !filename.endsWith(".xls"))) {
            result.put("success", false);
            result.put("message", "文件格式错误，仅支持Excel文件（.xlsx或.xls）");
            return result;
        }

        final int[] successCount = {0};
        final int[] updateCount = {0};
        final int[] failCount = {0};
        final List<String> errorMessages = new ArrayList<>();
        final Map<String, Long> existingRecords = new HashMap<>();

        log.info("开始导入合同人员Excel，更新模式：{}", updateIfExists ? "存在则更新" : "存在则跳过");

        try (InputStream inputStream = file.getInputStream()) {
            EasyExcel.read(inputStream, ContractPersonnelExcelDTO.class, new ReadListener<ContractPersonnelExcelDTO>() {

                @Override
                public void invoke(ContractPersonnelExcelDTO data, AnalysisContext context) {
                    Integer rowNum = context.readRowHolder().getRowIndex() + 1;

                    try {
                        String validationError = validateData(data, rowNum);
                        if (validationError != null) {
                            errorMessages.add(validationError);
                            failCount[0]++;
                            return;
                        }

                        // 使用合同ID+姓名+角色作为唯一标识
                        String uniqueKey = data.getContractId() + "_" + data.getName() + "_" + data.getRole();
                        if (existingRecords.containsKey(uniqueKey)) {
                            errorMessages.add(String.format("第%d行：合同人员 [%s-%s-%s] 在Excel中重复", 
                                rowNum, data.getContractId(), data.getName(), data.getRole()));
                            failCount[0]++;
                            return;
                        }

                        // 检查数据库中是否已存在
                        String checkSql = "SELECT id FROM contract_personnel WHERE contract_id = ? AND name = ? AND role = ?";
                        List<Map<String, Object>> existingList = jdbcTemplate.queryForList(checkSql, 
                            data.getContractId(), data.getName(), data.getRole());
                        boolean exists = !existingList.isEmpty();
                        Long existingId = exists ? ((Number) existingList.get(0).get("id")).longValue() : null;

                        if (exists) {
                            if (updateIfExists) {
                                String updateSql = "UPDATE contract_personnel SET " +
                                        "type = ?, department = ?, start_date = ?, end_date = ?, " +
                                        "price = ?, price_type = ?, contact_person_id = ?, updated_at = NOW() " +
                                        "WHERE id = ?";

                                jdbcTemplate.update(updateSql,
                                        data.getType(), data.getDepartment(), data.getStartDate(), data.getEndDate(),
                                        data.getPrice(), data.getPriceType(), data.getContactPersonId(),
                                        existingId
                                );

                                updateCount[0]++;
                                log.info("成功更新合同人员：{} - {} - {}", data.getContractId(), data.getName(), data.getRole());
                            } else {
                                errorMessages.add(String.format("第%d行：合同人员 [%s-%s-%s] 已存在于数据库中（跳过）",
                                        rowNum, data.getContractId(), data.getName(), data.getRole()));
                                failCount[0]++;
                                return;
                            }
                        } else {
                            String insertSql = "INSERT INTO contract_personnel (contract_id, name, type, role, department, " +
                                    "start_date, end_date, price, price_type, contact_person_id, created_at, updated_at) " +
                                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";

                            jdbcTemplate.update(insertSql,
                                    data.getContractId(), data.getName(), data.getType(), data.getRole(), data.getDepartment(),
                                    data.getStartDate(), data.getEndDate(), data.getPrice(), data.getPriceType(), 
                                    data.getContactPersonId()
                            );

                            successCount[0]++;
                            log.info("成功新增合同人员：{} - {} - {}", data.getContractId(), data.getName(), data.getRole());
                        }

                        existingRecords.put(uniqueKey, existingId);

                    } catch (Exception e) {
                        log.error("导入第{}行数据失败：{}", rowNum, e.getMessage(), e);
                        errorMessages.add(String.format("第%d行：导入失败 - %s", rowNum, e.getMessage()));
                        failCount[0]++;
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    log.info("合同人员Excel读取完成，新增：{}条，更新：{}条，失败：{}条",
                            successCount[0], updateCount[0], failCount[0]);
                }
            }).sheet().doRead();

            int totalSuccess = successCount[0] + updateCount[0];
            String message = updateIfExists ?
                    String.format("导入完成！新增：%d条，更新：%d条，失败：%d条", successCount[0], updateCount[0], failCount[0]) :
                    String.format("导入完成！成功：%d条，失败：%d条", successCount[0], failCount[0]);

            result.put("success", true);
            result.put("message", message);
            result.put("successCount", successCount[0]);
            result.put("updateCount", updateCount[0]);
            result.put("totalSuccess", totalSuccess);
            result.put("failCount", failCount[0]);
            result.put("errors", errorMessages);

        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            result.put("success", false);
            result.put("message", "读取Excel文件失败：" + e.getMessage());
        } catch (Exception e) {
            log.error("导入Excel文件失败", e);
            result.put("success", false);
            result.put("message", "导入失败：" + e.getMessage());
        }

        return result;
    }

    private String validateData(ContractPersonnelExcelDTO data, Integer rowNum) {
        if (data.getContractId() == null) {
            return String.format("第%d行：合同ID不能为空", rowNum);
        }

        if (data.getName() == null || data.getName().trim().isEmpty()) {
            return String.format("第%d行：姓名不能为空", rowNum);
        }

        if (data.getType() == null || data.getType().trim().isEmpty()) {
            return String.format("第%d行：类型不能为空", rowNum);
        }

        if (data.getRole() == null || data.getRole().trim().isEmpty()) {
            return String.format("第%d行：角色不能为空", rowNum);
        }

        if (data.getDepartment() == null || data.getDepartment().trim().isEmpty()) {
            return String.format("第%d行：部门不能为空", rowNum);
        }

        if (data.getStartDate() == null) {
            return String.format("第%d行：开始日期不能为空", rowNum);
        }

        if (data.getEndDate() == null) {
            return String.format("第%d行：结束日期不能为空", rowNum);
        }

        if (data.getPrice() == null) {
            return String.format("第%d行：价格不能为空", rowNum);
        }

        if (data.getPriceType() == null || data.getPriceType().trim().isEmpty()) {
            return String.format("第%d行：价格类型不能为空", rowNum);
        }

        if (data.getContactPersonId() == null) {
            return String.format("第%d行：联系人ID不能为空", rowNum);
        }

        return null;
    }

    @Override
    public byte[] downloadTemplate() {
        ByteArrayOutputStream outputStream = null;
        try {
            outputStream = new ByteArrayOutputStream();

            List<ContractPersonnelExcelDTO> templateData = new ArrayList<>();

            ContractPersonnelExcelDTO example = new ContractPersonnelExcelDTO();
            example.setContractId(1L);
            example.setName("张三");
            example.setType("开发人员");
            example.setRole("Java工程师");
            example.setDepartment("技术部");
            example.setStartDate(new Date());
            example.setEndDate(new Date());
            example.setPrice(new BigDecimal("500.00"));
            example.setPriceType("日薪");
            example.setContactPersonId(1L);
            templateData.add(example);

            EasyExcel.write(outputStream, ContractPersonnelExcelDTO.class)
                    .sheet("合同人员导入模板")
                    .doWrite(templateData);

            return outputStream.toByteArray();

        } catch (Exception e) {
            log.error("生成模板文件失败", e);
            throw new RuntimeException("生成模板文件失败：" + e.getMessage(), e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("关闭输出流失败", e);
                }
            }
        }
    }
}

